from dao.BaseDao import BaseDao
from Static import Static
import pandas as pd
from util import CommonHelper


class UserDao(BaseDao):
    def initDB(self):
        self.__createTableUser()

    def __createTableUser(self):
        sql = """
            CREATE TABLE IF NOT EXISTS {} (
                `id` int NOT NULL AUTO_INCREMENT,
                `username` VARCHAR ( 20 ),
                `nickname` VARCHAR ( 20 ),
                `password` VARCHAR ( 256 ),
                `phone` VARCHAR ( 20 ) DEFAULT '-',
                `gender` VARCHAR ( 20 ) DEFAULT '1',
                `dept_id` int,
                `role` VARCHAR ( 20 ) DEFAULT '1',
                `state` VARCHAR ( 20 ) DEFAULT '1',
            PRIMARY KEY ( `id` ) 
            ) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;""".format(
            Static.DB_User
        )
        self.executeNoResult(sql)

    def importUsers(self, df):
        self.__createTableUser()
        self.write2sql(df, Static.DB_User)

    def updateUser(self, id, user, nick, phone, gender, dept_id, role):
        self.__createTableUser()
        sql_query = """
            UPDATE `{}`
            SET
                username = '{}',
                nickname = '{}',
                phone = '{}',
                gender = '{}',
                dept_id = '{}',
                role='{}'
            WHERE
                id = {}
        """.format(
            Static.DB_User, user, nick, phone, gender, dept_id, role, id
        )
        self.executeNoResult(sql_query)

    # 验证用户名和密码是否正确,正确返回nickname,错误返回False
    def verifyUser(self, username, pwd):
        self.__createTableUser()
        sql_query = "SELECT `username` from {} WHERE `username`='{}' AND `password`='{}' AND `state`='1'".format(
            Static.DB_User, username, pwd
        )
        df = self.readSQL(sql_query)
        if df.shape[0] == 0:
            return False
        else:
            username = df.iloc[0]["username"]
            return username

    def queryUsers(self, name, dept):
        self.__createTableUser()
        sql_query = """
            SELECT
                u.id,
                u.username,
                u.nickname,
                u.phone,
                u.gender,
                u.state,
                u.dept_id,
                u.role,
                d.`name` department,
	            s_role.dictLabel roleStr ,
                s_gender.dictLabel genderStr 
            FROM
                `{}` u
                LEFT JOIN department d ON u.dept_id = d.id
                LEFT JOIN ( SELECT * FROM `{}` WHERE `key` = 'sys_role' ) s_role ON s_role.dictValue = u.role
                LEFT JOIN ( SELECT * FROM `{}` WHERE `key` = 'sys_user_sex' ) s_gender ON s_gender.dictValue = u.gender""".format(
            Static.DB_User, Static.DB_SystemDict, Static.DB_SystemDict
        )
        where_clause = []
        if name:
            where_clause.append(f"u.`nickname` LIKE '%%{name}%%'")
        if dept:
            where_clause.append(f"d.`name` LIKE '%%{dept}%%'")
        if where_clause:
            sql_query += f" WHERE {' AND '.join(where_clause)}"
        df = self.readSQL(sql_query)
        return df

    def getAllUsers(self):
        self.__createTableUser()
        sql_query = "SELECT * from {}".format(Static.DB_User)
        df = self.readSQL(sql_query)
        return df

    def getUserId(self, name):
        self.__createTableUser()
        sql_query = "SELECT * from {} WHERE nickname='{}'".format(Static.DB_User, name)
        df = self.readSQL(sql_query)
        try:
            id = df.iloc[0]["id"]
            return id
        except Exception as e:
            return -1

    def getUserInfoByUserName(self, userName):
        self.__createTableUser()
        sql_query = """
            SELECT
                u.id userId,
                u.dept_id deptId,
                u.username,
                u.nickname,
                u.phone,
                u.gender,
                u.role,
                s_role.dictLabel roleStr
            FROM
                `{}` u
                LEFT JOIN ( SELECT * FROM `{}` WHERE `key` = 'sys_role' ) s_role ON s_role.dictValue = u.role
            WHERE
                u.username = '{}'""".format(
            Static.DB_User, Static.DB_SystemDict, userName
        )
        df = self.readSQL(sql_query)
        return df

    def getUserInfo(self, userId):
        self.__createTableUser()
        sql_query = """
            SELECT
                u.id,
                u.username,
                u.nickname,
                u.phone,
                u.gender,
                u.state,
                u.role,
                u.dept_id
            FROM
                `{}` u
            WHERE
                u.id = {}""".format(
            Static.DB_User, userId
        )
        df = self.readSQL(sql_query)
        return df

    # 重置密码
    def resetPwd(self, userId, pwd):
        self.__createTableUser()
        sql_query = """
            UPDATE `{}`
            SET
                password = '{}'
            WHERE
                `id` = {}
        """.format(
            Static.DB_User, pwd, userId
        )
        print(sql_query)
        self.executeNoResult(sql_query)

    def deleteUser(self, userId):
        self.__createTableUser()
        sql_query = """
            DELETE FROM `{}` WHERE id = {}
        """.format(
            Static.DB_User, userId
        )
        self.executeNoResult(sql_query)

    def changeUserState(self, userId, state):
        self.__createTableUser()
        sql_query = """
            UPDATE `{}`
            SET
                state = '{}'
            WHERE
                id = {}
        """.format(
            Static.DB_User, state, userId
        )
        self.executeNoResult(sql_query)
